alter table MessageCenter
add DeviceToken varchar(500) null
go

create VIEW vwUserDeviceToken
AS

select [User].[Login], Ping.UserId, max(Ping.DeviceToken) as DeviceToken
from (select UserId, MAX(DatePing) AS DatePingLast
	from Ping (nolock)
	group by UserId) as vwPing inner join Ping (nolock) on vwPing.UserId = Ping.UserId and vwPing.DatePingLast = Ping.DatePing
								inner join [User] (nolock) on Ping.UserId = [User].UserId
group by [User].[Login], Ping.UserId
GO

ALTER PROCEDURE [dbo].[spMessageCenter_SystemUser_Add]
(
	@Subject VARCHAR(300),
	@Body VARCHAR(MAX),
	@FromSystemLogin VARCHAR(50),
	@ToUserLogin VARCHAR(50)
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @CreatedDate DATETIME
	SET @CreatedDate = GETUTCDATE()
	
	declare @DeviceToken varchar(500)
	
	select @DeviceToken = DeviceToken
	from vwUserDeviceToken
	where [Login] = @ToUserLogin

	INSERT INTO MessageCenter
	(CreatedDate, DateReceive, [Subject], Body, [Status], FromSystemLogin, ToUserLogin, DeviceToken)
	VALUES (@CreatedDate, @CreatedDate, @Subject, @Body, 'New', @FromSystemLogin, @ToUserLogin, @DeviceToken)
END
go

ALTER PROCEDURE [dbo].[spMessageCenter_SystemUser_Add_All_User]
(
	@Subject VARCHAR(300),
	@Body VARCHAR(MAX),
	@FromLogin VARCHAR(50)
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @CreatedDate DATETIME
	SET @CreatedDate = GETUTCDATE()
	
	INSERT INTO MessageCenter
	(CreatedDate, DateReceive, [Subject], Body, [Status], FromSystemLogin, ToUserLogin, DeviceToken)
	SELECT @CreatedDate, @CreatedDate, @Subject, @Body, 'New', @FromLogin, [User].[Login], vwUserDeviceToken.DeviceToken
	FROM [User] inner join vwUserDeviceToken on [User].UserId = vwUserDeviceToken.UserId
	WHERE [User].IsActive = 1
		AND [User].Deleted = 0
END
go

ALTER PROCEDURE [dbo].[spMessageCenter_SystemUser_Add_All]
(
	@Subject VARCHAR(300),
	@Body VARCHAR(MAX),
	@FromLogin VARCHAR(50)
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @CreatedDate DATETIME
	SET @CreatedDate = GETUTCDATE()
	
	INSERT INTO MessageCenter
	(CreatedDate, DateReceive, [Subject], Body, [Status], FromSystemLogin, ToUserLogin, DeviceToken)
	
	SELECT @CreatedDate, @CreatedDate, @Subject, @Body, 'New', @FromLogin, [User].[Login], vwUserDeviceToken.DeviceToken
	FROM [User] inner join vwUserDeviceToken on [User].UserId = vwUserDeviceToken.UserId
	WHERE [User].IsActive = 1
		AND [User].Deleted = 0
		
	UNION ALL
	
	SELECT @CreatedDate, @CreatedDate, @Subject, @Body, 'New', @FromLogin, [Login], NULL
	FROM Business
	WHERE IsActive = 1
		AND Deleted = 0
END
go